Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# UPDATE Statement

The `UPDATE` statement changes the values of specified columns in one or more rows in a table or view. For a full description of the `UPDATE` SQL statement, see Oracle Database SQL Reference.

Syntax

update statement ::=

Description of the illustration update_statement.gif

Keyword and Parameter Description

alias

Another (usually short) name for the referenced table or view, typically used in the `WHERE` clause.

column_name

The column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the `column_name` list. Column names need not appear in the `UPDATE` statement in the same order that they appear in the table or view.

returning_clause

Returns values from updated rows, eliminating the need to `SELECT` the rows afterward. You can retrieve the column values into variables or host variables, or into collections or host arrays. You cannot use the `RETURNING` clause for remote or parallel updates. If the statement does not affect any rows, the values of the variables specified in the `RETURNING` clause are undefined. For the syntax of `returning_clause`, see "RETURNING INTO Clause".

SET column_name = sql_expression

This clause assigns the value of `sql_expression` to the column identified by `column_name`. If `sql_expression` contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.

SET column_name = (subquery3)

Assigns the value retrieved from the database by `subquery3` to the column identified by `column_name`. The subquery must return exactly one row and one column.

SET (column_name, column_name, ...) = (subquery4)

Assigns the values retrieved from the database by `subquery4` to the columns in the `column_name` list. The subquery must return exactly one row that includes all the columns listed. The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on.

sql_expression

Any valid SQL expression. For more information, see Oracle Database SQL Reference.

subquery

A `SELECT` statement that provides a set of rows for processing. Its syntax is like that of `select_into_statement` without the `INTO` clause. See "SELECT INTO Statement".

table_reference

A table or view that must be accessible when you execute the `UPDATE` statement, and for which you must have `UPDATE` privileges. For the syntax of `table_reference`, see "DELETE Statement".

TABLE (subquery2)

The operand of `TABLE` is a `SELECT` statement that returns a single column value, which must be a nested table or a varray. Operator `TABLE` informs Oracle that the value is a collection, not a scalar value.

WHERE CURRENT OF cursor_name

Refers to the latest row processed by the `FETCH` statement associated with the specified cursor. The cursor must be `FOR` `UPDATE` and must be open and positioned on a row. If the cursor is not open, the `CURRENT` `OF` clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception `NO_DATA_FOUND`.

WHERE search_condition

Chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit this clause, all rows in the table are updated.

Usage Notes

You can use the `UPDATE` `WHERE` `CURRENT` `OF` statement after a fetch from an open cursor (including fetches done by a cursor `FOR` loop), provided the associated query is `FOR` `UPDATE`. This statement updates the row that was just fetched.

The implicit cursor `SQL` and the cursor attributes `%NOTFOUND`, `%FOUND`, `%ROWCOUNT`, and `%ISOPEN` let you access useful information about the execution of an `UPDATE` statement.

Examples

Example 13-7 creates a table with correct employee IDs but garbled names. Then it runs an `UPDATE` statement with a correlated query, to retrieve the correct names from the `EMPLOYEES` table and fix the names in the new table.

Example 13-7 Using UPDATE With a Subquery

    -- Create a table with all the right IDs, but messed-up names
    CREATE TABLE employee_temp AS
      SELECT employee_id, UPPER(first_name) first_name,
        TRANSLATE(last_name,'aeiou','12345') last_name
        FROM employees;
    BEGIN
    -- Display the first 5 names to show they're messed up
       FOR person IN (SELECT * FROM employee_temp WHERE ROWNUM < 6)
       LOOP
          DBMS_OUTPUT.PUT_LINE(person.first_name || ' ' || person.last_name);
       END LOOP;
       UPDATE employee_temp SET (first_name, last_name) =
          (SELECT first_name, last_name FROM employees
             WHERE employee_id = employee_temp.employee_id);
       DBMS_OUTPUT.PUT_LINE('*** Updated ' || SQL%ROWCOUNT || ' rows. ***');
    -- Display the first 5 names to show they've been fixed up
       FOR person IN (SELECT * FROM employee_temp WHERE ROWNUM < 6)
       LOOP
          DBMS_OUTPUT.PUT_LINE(person.first_name || ' ' || person.last_name);
       END LOOP;
    END;
    /

For examples, see the following:

Example 1-12, "Creating a Stored Subprogram"
Example 4-1, "Using a Simple IF-THEN Statement"
Example 5-51, "Updating a Row Using a Record"
Example 5-52, "Using the RETURNING Clause with a Record"
Example 6-1, "Data Manipulation With PL/SQL"
Example 6-5, "Using CURRVAL and NEXTVAL"
Example 6-6, "Using ROWNUM"
Example 6-38, "Using SAVEPOINT With ROLLBACK"
Example 6-41, "Using CURRENT OF to Update the Latest Row Fetched From a Cursor"
Example 7-1, "Examples of Dynamic SQL"
Example 7-5, "Dynamic SQL with RETURNING BULK COLLECT INTO Clause"
Example 7-6, "Dynamic SQL Inside FORALL Statement"
Example 11-6, "Using Rollbacks With FORALL"
Example 11-9, "Bulk Operation That Continues Despite Exceptions"

Related Topics

"Data Manipulation"
"DELETE Statement"
"FETCH Statement"
"INSERT Statement"